SELECT column FROM table; |
從單一資料表選擇欄位 |
SELECT username FROM employees; |
| 指定欄位避免衝突 |
指定要查詢的資料表與欄位（可避免欄位名稱衝突） |
SELECT employees.employee_id, machines.employee_id FROM ... |
ORDER BY |
Sequences the records returned by a query based on a specified column or columns |
ORDER BY department ORDER BY city DESC ORDER BY country, city |
WHERE X > Y |
|
WHERE birthdate > '1970-01-01' |
WHERE X >= Y |
|
WHERE birthdate >= '1970-01-01' |
WHERE X < Y |
|
WHERE date < '2023-01-31' |
WHERE X <= Y |
|
WHERE date <= '2023-01-31' |
WHERE Ｘ LIKE 'Ｙ%' |
關鍵字查詢 |
WHERE title LIKE 'IT%' |
WHERE NOT X ='Ｙ' |
a value in the country column that is not 'Mexico' |
WHERE NOT country = 'Mexico' |
<> (not equal to)or != |
!= |
WHERE date <> '2023-02-28' WHERE date != '2023-05-14' |
OR |
that either condition can be met |
WHERE country = 'C' OR country = 'U' |
| 'a%' |
以字母 a 開頭，後面可接任意數量字元 |
匹配 apple、abc、a123，但不匹配 ba SELECT * FROM employees WHERE name LIKE 'a%'; |
| '%a' |
以字母 a 結尾，前面可有任意數量字元 |
匹配 ba、coca，但不匹配 ab |
| '%a%' |
含有字母 a，前後可有任意數量字元 |
匹配 apple、cat、banana，但不匹配 bbc |
| 'a_' |
以字母 a 開頭，後面接一個任意字元 |
匹配 ab、a1、aX，但不匹配 a、abc |
| 'a__' |
以字母 a 開頭，後面接兩個任意字元 |
匹配 abc、a12，但不匹配 ab、abcd |
| '_a' |
前面有一個任意字元，a 為結尾 |
匹配 ba、1a，但不匹配 aa、bba |
| 'a' |
a 前後各有一個任意字元 |
匹配 bab、1a2、XaZ，但不匹配 aab、ab、aa |
WHERE...AND |
Specifies that both conditions must be met simultaneously in a flter that contains two conditions |
WHERE region = 5 AND country = 'USA' |
WHERE...BETWEEN..AND |
Filters for numbers or dates within a range; BETWEEN is followed by the first value to include in the range, the AND operator, and the last value to include in the range |
WHERE hiredate BETWEEN '2002-01-01' AND '2003-01-01' |
NULL |
表示欄位值缺失或未指派 |
SELECT * FROM machines WHERE employee_id IS NULL; |
INNER JOIN |
只回傳兩個資料表中符合條件的行（交集），僅返回有匹配的行 |
SELECT ... FROM employees INNER JOIN machines ON employees.device_id = machines.device_id; |
ON table1.column = table2.column |
指定 JOIN 時兩個資料表的連接條件 |
ON employees.device_id = machines.device_id |
| LEFT JOIN |
返回左表（FROM 後面那個表）的所有紀錄，右表不匹配的顯示 NULL |
SELECT ... FROM employees LEFT JOIN machines ON employees.device_id = machines.device_id; |
| RIGHT JOIN |
返回右表（JOIN 那個表）的所有紀錄，左表不匹配的顯示 NULL |
SELECT ... FROM employees RIGHT JOIN machines ON employees.device_id = machines.device_id; |
| FULL OUTER JOIN |
返回兩個表的所有紀錄，不匹配的顯示 NULL |
SELECT ... FROM employees FULL OUTER JOIN machines ON employees.device_id = machines.device_id; |
yennefer
iThome鐵人賽
看影片追技術